USE [SystemScanning];
GO
-- --------------------------------------------------
-- Table creation script for RM 11148
-- --------------------------------------------------


IF SCHEMA_ID(N'SystemScanning') IS NULL EXECUTE(N'CREATE SCHEMA [SystemScanning]');
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Computers'
CREATE TABLE [SystemScanning].[Computer] (
    [ComputerID] int IDENTITY(1,1) NOT NULL,
    [Sid] varchar(128)  NOT NULL,
    [Hostname] varchar(1024)  NOT NULL,
    [DomainName] varchar(1024)  NOT NULL
);
GO

-- Creating table 'Hotfix'
CREATE TABLE [SystemScanning].[Hotfix] (
    [HotfixID] int IDENTITY(1,1) NOT NULL,
    [Identifier] varchar(100)  NOT NULL,
    [Description] varchar(500)  NOT NULL,
    [InformationalUrl] varchar(1000)  NOT NULL,
    [Synopsis] varchar(max)  NULL
);
GO

CREATE TABLE [SystemScanning].[Service] (
    [ServiceID] int IDENTITY(1,1) NOT NULL,
    [Identifier] varchar(300)  NOT NULL,
    [Name] varchar(500)  NOT NULL
);
GO

-- Creating table 'OperatingSystemComponents'
CREATE TABLE [SystemScanning].[OperatingSystemComponent] (
    [OperatingSystemComponentID] int IDENTITY(1,1) NOT NULL,
    [Identifier] varchar(300)  NOT NULL,
    [Name] varchar(300)  NOT NULL
);
GO

-- Creating table 'Scans'
CREATE TABLE [SystemScanning].[Scan] (
    [ScanID] int IDENTITY(1,1) NOT NULL,
    [Performed] datetimeoffset  NOT NULL,
    [ComputerID] int  NOT NULL
);
GO

-- Creating table 'SoftwareApplications'
CREATE TABLE [SystemScanning].[SoftwareApplication] (
    [SoftwareApplicationID] int IDENTITY(1,1) NOT NULL,
    [Identifier] varchar(100)  NOT NULL,
    [Name] varchar(300)  NOT NULL,
    [Version] varchar(50)  NOT NULL,
    [Vendor] varchar(300)  NOT NULL
);
GO

-- Creating table 'SystemChangeLogs'
CREATE TABLE [SystemScanning].[SystemChangeLog] (
    [SystemChangeLogID] int IDENTITY(1,1) NOT NULL,
    [SystemChangeTypeID] int  NOT NULL,
	 [ChangedObjectID] int not null,
    [ScanID] int  NOT NULL
);
GO

-- Creating table 'SystemChangeTypes'
CREATE TABLE [SystemScanning].[SystemChangeType] (
    [SystemChangeTypeID] int IDENTITY(1,1) NOT NULL,
    [Description] varchar(50)  NOT NULL
);
GO

-- Creating table 'HotfixScan'
CREATE TABLE [SystemScanning].[ScanHotfix] (
    [HotfixID] int  NOT NULL,
    [ScanID] int  NOT NULL
);
GO
CREATE TABLE [SystemScanning].[ScanService] (
    [ServiceID] int  NOT NULL,
    [ScanID] int  NOT NULL,
    [State] varchar(100) not null,
    [Status] varchar(100) not null,
    [StartMode] varchar(50) not null,
    [LogonName] varchar(300) not null
);
go
-- Creating table 'ScanOperatingSystemComponent'
CREATE TABLE [SystemScanning].[ScanOperatingSystemComponent] (
    [OperatingSystemComponentID] int  NOT NULL,
    [ScanID] int  NOT NULL
);
GO

-- Creating table 'ScanSoftwareApplication'
CREATE TABLE [SystemScanning].[ScanSoftwareApplication] (
    [ScanID] int  NOT NULL,
    [SoftwareApplicationID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ServiceID] in table 'Service'
ALTER TABLE [SystemScanning].[Service]
ADD CONSTRAINT [PK_Service]
    PRIMARY KEY NONCLUSTERED ([ServiceID] ASC),
 CONSTRAINT [UNQ_Service_Identifier]
    UNIQUE CLUSTERED ([Identifier] ASC);
GO
-- Creating primary key on [ComputerID] in table 'Computers'
ALTER TABLE [SystemScanning].[Computer]
ADD CONSTRAINT [PK_Computer]
    PRIMARY KEY CLUSTERED ([ComputerID] ASC);
GO

-- Creating primary key on [HotfixID] in table 'Hotfix'
ALTER TABLE [SystemScanning].[Hotfix]
ADD CONSTRAINT [PK_Hotfix]
    PRIMARY KEY NONCLUSTERED ([HotfixID] ASC),
	  CONSTRAINT [UNQ_Hotfix_Identifier]
    UNIQUE CLUSTERED ([Identifier] ASC);
GO

-- Creating primary key on [OperatingSystemComponentID] in table 'OperatingSystemComponents'
ALTER TABLE [SystemScanning].[OperatingSystemComponent]
ADD CONSTRAINT [PK_OperatingSystemComponent]
    PRIMARY KEY NONCLUSTERED ([OperatingSystemComponentID] ASC),
	  CONSTRAINT [UNQ_OperatingSystemComponent_Identifier]
    UNIQUE CLUSTERED ([Identifier] ASC);
GO

-- Creating primary key on [ScanID] in table 'Scans'
ALTER TABLE [SystemScanning].[Scan]
ADD CONSTRAINT [PK_Scan]
    PRIMARY KEY CLUSTERED ([ScanID] ASC);
GO

-- Creating primary key on [SoftwareApplicationID] in table 'SoftwareApplications'
ALTER TABLE [SystemScanning].[SoftwareApplication]
ADD CONSTRAINT [PK_SoftwareApplication]
    PRIMARY KEY NONCLUSTERED ([SoftwareApplicationID] ASC),
	  CONSTRAINT [UNQ_SoftwareApplication_Identifier]
    UNIQUE CLUSTERED ([Identifier] ASC);
GO

-- Creating primary key on [SystemChangeLogID] in table 'SystemChangeLogs'
ALTER TABLE [SystemScanning].[SystemChangeLog]
ADD CONSTRAINT [PK_SystemChangeLog]
    PRIMARY KEY CLUSTERED ([SystemChangeLogID] ASC);
GO

-- Creating primary key on [SystemChangeTypeID] in table 'SystemChangeTypes'
ALTER TABLE [SystemScanning].[SystemChangeType]
ADD CONSTRAINT [PK_SystemChangeType]
    PRIMARY KEY CLUSTERED ([SystemChangeTypeID] ASC),
    constraint [UNQ_SystemChangeType_Description]
    unique nonclustered (description)   ;
GO

-- Creating primary key on [HotfixID], [ScanID] in table 'HotfixScan'
ALTER TABLE [SystemScanning].[ScanHotfix]
ADD CONSTRAINT [PK_HotfixScan]
    PRIMARY KEY NONCLUSTERED ([HotfixID], [ScanID] ASC);
GO
ALTER TABLE [SystemScanning].[ScanService]
ADD CONSTRAINT [PK_ScanService]
    PRIMARY KEY NONCLUSTERED ([ServiceID], [ScanID] ASC);
GO

-- Creating primary key on [OperatingSystemComponents_OperatingSystemComponentID], [ScanID] in table 'ScanOperatingSystemComponent'
ALTER TABLE [SystemScanning].[ScanOperatingSystemComponent]
ADD CONSTRAINT [PK_ScanOperatingSystemComponent]
    PRIMARY KEY NONCLUSTERED ([OperatingSystemComponentID], [ScanID] ASC);
GO

-- Creating primary key on [ScanID], [SoftwareApplicationID] in table 'ScanSoftwareApplication'
ALTER TABLE [SystemScanning].[ScanSoftwareApplication]
ADD CONSTRAINT [PK_ScanSoftwareApplication]
    PRIMARY KEY NONCLUSTERED ([ScanID], [SoftwareApplicationID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ComputerID] in table 'Scans'
ALTER TABLE [SystemScanning].[Scan]
ADD CONSTRAINT [FK_Computer_Scan]
    FOREIGN KEY ([ComputerID])
    REFERENCES [SystemScanning].[Computer]
        ([ComputerID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Computer_Scan'
CREATE INDEX [IX_FK_Computer_Scan]
ON [SystemScanning].[Scan]
    ([ComputerID]);
GO

-- Creating foreign key on [SystemChangeTypeID] in table 'SystemChangeLogs'
ALTER TABLE [SystemScanning].[SystemChangeLog]
ADD CONSTRAINT [FK_SystemChangeType_SystemChangeLog]
    FOREIGN KEY ([SystemChangeTypeID])
    REFERENCES [SystemScanning].[SystemChangeType]
        ([SystemChangeTypeID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SystemChangeType_SystemChangeLog'
CREATE INDEX [IX_FK_SystemChangeType_SystemChangeLog]
ON [SystemScanning].[SystemChangeLog]
    ([SystemChangeTypeID]);
GO

-- Creating foreign key on [Hotfix_HotfixID] in table 'HotfixScan'
ALTER TABLE [SystemScanning].[ScanHotfix]
ADD CONSTRAINT [FK_HotfixScan_Hotfix]
    FOREIGN KEY ([HotfixID])
    REFERENCES [SystemScanning].[Hotfix]
        ([HotfixID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [SystemScanning].[ScanService]
ADD CONSTRAINT [FK_ScanService_Service]
    FOREIGN KEY ([ServiceID])
    REFERENCES [SystemScanning].[Service]
        ([ServiceID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Scan_ScanID] in table 'HotfixScan'
ALTER TABLE [SystemScanning].[ScanHotfix]
ADD CONSTRAINT [FK_HotfixScan_Scan]
    FOREIGN KEY ([ScanID])
    REFERENCES [SystemScanning].[Scan]
        ([ScanID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

	 ALTER TABLE [SystemScanning].[ScanService]
ADD CONSTRAINT [FK_ScanService_Scan]
    FOREIGN KEY ([ScanID])
    REFERENCES [SystemScanning].[Scan]
        ([ScanID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HotfixScan_Scans'
CREATE INDEX [IX_FK_HotfixScan_Scan]
ON [SystemScanning].[ScanHotfix]
    ([ScanID]);
GO
CREATE INDEX [IX_FK_ScanService_Scan]
ON [SystemScanning].[ScanService]
    ([ScanID]);
GO

-- Creating foreign key on [OperatingSystemComponents_OperatingSystemComponentID] in table 'ScanOperatingSystemComponent'
ALTER TABLE [SystemScanning].[ScanOperatingSystemComponent]
ADD CONSTRAINT [FK_ScanOperatingSystemComponent_OperatingSystemComponent]
    FOREIGN KEY ([OperatingSystemComponentID])
    REFERENCES [SystemScanning].[OperatingSystemComponent]
        ([OperatingSystemComponentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ScanID] in table 'ScanOperatingSystemComponent'
ALTER TABLE [SystemScanning].[ScanOperatingSystemComponent]
ADD CONSTRAINT [FK_ScanOperatingSystemComponent_Scan]
    FOREIGN KEY ([ScanID])
    REFERENCES [SystemScanning].[Scan]
        ([ScanID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScanOperatingSystemComponent_Scans'
CREATE INDEX [IX_FK_ScanOperatingSystemComponent_Scan]
ON [SystemScanning].[ScanOperatingSystemComponent]
    ([ScanID]);
GO

-- Creating foreign key on [ScanID] in table 'ScanSoftwareApplication'
ALTER TABLE [SystemScanning].[ScanSoftwareApplication]
ADD CONSTRAINT [FK_ScanSoftwareApplication_Scan]
    FOREIGN KEY ([ScanID])
    REFERENCES [SystemScanning].[Scan]
        ([ScanID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [SoftwareApplications_SoftwareApplicationID] in table 'ScanSoftwareApplication'
ALTER TABLE [SystemScanning].[ScanSoftwareApplication]
ADD CONSTRAINT [FK_ScanSoftwareApplication_SoftwareApplications]
    FOREIGN KEY ([SoftwareApplicationID])
    REFERENCES [SystemScanning].[SoftwareApplication]
        ([SoftwareApplicationID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScanSoftwareApplication_SoftwareApplications'
CREATE INDEX [IX_FK_ScanSoftwareApplication_SoftwareApplication]
ON [SystemScanning].[ScanSoftwareApplication]
    ([SoftwareApplicationID]);
GO

-- Creating foreign key on [ScanID] in table 'SystemChangeLogs'
ALTER TABLE [SystemScanning].[SystemChangeLog]
ADD CONSTRAINT [FK_ScanSystemChangeLog]
    FOREIGN KEY ([ScanID])
    REFERENCES [SystemScanning].[Scan]
        ([ScanID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ScanSystemChangeLog'
CREATE INDEX [IX_FK_ScanSystemChangeLog]
ON [SystemScanning].[SystemChangeLog]
    ([ScanID]);
GO

-- --------------------------------------------------
-- Create duh Views
-- --------------------------------------------------


if (object_id('systemscanning.LastTwoScansByComputer') is not null)
	drop view SystemScanning.LastTwoScansByComputer;
go
create view SystemScanning.LastTwoScansByComputer
/******************************************************************************************************
View		: SystemScanning.LastTwoScansByComputer
Author		: pwilkins
Created		: Sep 2010, RM 11148
Description	: Returns info about the last two scans performed on a per computer basis.

Modified	: 
*******************************************************************************************************/
as
	SELECT  
		c.Hostname,
		c.ComputerID,
		scan_l.Performed LatestScanPerformed, 
		scan_l.ScanID as LatestScanID,
		scan_p.Performed PreviousScanPerformed,
		scan_p.ScanID as PreviousScanID
	FROM    SystemScanning.Computer c
	INNER JOIN SystemScanning.Scan scan_l on 
		scan_l.ScanID = (select MAX(scanid) from SystemScanning.Scan where ComputerID = c.ComputerID)
	left outer JOIN SystemScanning.Scan scan_p on 
		scan_p.ScanID = (select MAX(scanid) 
		from SystemScanning.Scan where ComputerID = c.ComputerID
		and scan_l.ScanID > ScanID)
go


-- --------------------------------------------------
-- Create lookup table values
-- --------------------------------------------------

insert SystemScanning.SystemChangeType (Description) values
('Added SoftwareApplication'),
('Removed SoftwareApplication'),
('Added Hotfix'),
('Removed Hotfix'),
('Added Service'),
('Removed Service'),
('Added OperatingSystemComponent'),
('Removed OperatingSystemComponent')
go

alter table    SystemScanning.Scan
add ScannerVersion varchar(100) not null,
constraint DF_SystemScanning_ScannerVersion  default('Unknown') for scannerversion
go